
if exists(select name from sys.objects where type = 'P' and name = 'sp_WebPlus_Skills_Reporting_ByStudent_Select')
BEGIN
	DROP PROCEDURE [sp_WebPlus_Skills_Reporting_ByStudent_Select]
END
GO



SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [sp_WebPlus_Skills_Reporting_ByStudent_Select]
  @AcademicYearID   AS VARCHAR(5)
AS
--AMG 15/01/2024
--The DataSource for the main reporting page in OneGradePlus for Skills
--We need to calculate the starting point and the MostRecentPoint
--So if a student starts in period 3, that is their starting point - we can then calculate the difference between the start and end point.
--I think this is the only report we need - a grouping report might get silly
--Only show the Overall Score for Student Answers plus Course Answers

----Now, (for Student) we need to find the Most Recent Period and the StartingValue and the Latest Value.
--SELECT
--OGP_StudentID,
--	(CASE WHEN Period1 IS NULL THEN
--		(CASE WHEN Period2 IS NULL THEN
--			(CASE WHEN Period3 IS NULL THEN
--				(CASE WHEN Period4 IS NULL THEN
--					(CASE WHEN Period5 IS NULL THEN
--						(CASE WHEN Period6 IS NULL THEN
--							(CASE WHEN Period7 IS NULL THEN
--								(CASE WHEN Period8 IS NULL THEN
--									(CASE WHEN Period9 IS NULL THEN
--										(CASE WHEN Period10 IS NULL THEN
--										NULL
--										ELSE Period10 END)
--									ELSE Period9 END)
--								ELSE Period8 END)
--							ELSE Period7 END) 
--						ELSE Period6 END)
--					ELSE Period5 END)
--				ELSE Period4 END)
--			ELSE Period3 END)
--		ELSE Period2 END)
--	ELSE Period1 END)

--AS StartingPeriodValue,

--	(CASE WHEN Period1 IS NULL THEN
--		(CASE WHEN  Period2 IS NULL THEN
--			(CASE WHEN  Period3 IS NULL THEN
--				(CASE WHEN  Period4 IS NULL THEN
--					(CASE WHEN  Period5 IS NULL THEN
--						(CASE WHEN  Period6 IS NULL THEN
--							(CASE WHEN  Period7 IS NULL THEN
--								(CASE WHEN  Period8 IS NULL THEN
--									(CASE WHEN  Period9 IS NULL THEN
--										(CASE WHEN  Period10 IS NULL THEN
--										NULL
--										ELSE 10 END)
--									ELSE 9 END)
--								ELSE 8 END)
--							ELSE 7 END) 
--						ELSE 6 END)
--					ELSE 5 END)
--				ELSE 4 END)
--			ELSE 3 END)
--		ELSE 2 END)
--	ELSE 1 END)
--AS StartingPeriod,

--	(CASE WHEN  Period10 IS NULL THEN
--		(CASE WHEN  Period9 IS NULL THEN
--			(CASE WHEN  Period8 IS NULL THEN
--				(CASE WHEN  Period7 IS NULL THEN
--					(CASE WHEN  Period6 IS NULL THEN
--						(CASE WHEN  Period5 IS NULL THEN
--							(CASE WHEN  Period4 IS NULL THEN
--								(CASE WHEN  Period3 IS NULL THEN
--									(CASE WHEN  Period2 IS NULL THEN
--										(CASE WHEN  Period1 IS NULL THEN
--										NULL
--										ELSE  Period1 END)
--									ELSE  Period2 END)
--								ELSE  Period3 END)
--							ELSE  Period4 END) 
--						ELSE  Period5 END)
--					ELSE  Period6 END)
--				ELSE  Period7 END)
--			ELSE  Period8 END)
--		ELSE  Period9 END)
--	ELSE  Period10 END)

--AS MostRecentPeriodValue,

--	(CASE WHEN  Period10 IS NULL THEN
--		(CASE WHEN  Period9 IS NULL THEN
--			(CASE WHEN  Period8 IS NULL THEN
--				(CASE WHEN  Period7 IS NULL THEN
--					(CASE WHEN  Period6 IS NULL THEN
--						(CASE WHEN  Period5 IS NULL THEN
--							(CASE WHEN  Period4 IS NULL THEN
--								(CASE WHEN  Period3 IS NULL THEN
--									(CASE WHEN  Period2 IS NULL THEN
--										(CASE WHEN  Period1 IS NULL THEN
--										NULL
--										ELSE 1 END)
--									ELSE 2 END)
--								ELSE 3 END)
--							ELSE 4 END) 
--						ELSE 5 END)
--					ELSE 6 END)
--				ELSE 7 END)
--			ELSE 8 END)
--		ELSE 9 END)
--	ELSE 6 END)
--AS MostRecentPeriod
--INTO #TempvSkillsStudentOnlyAnswerAvg_Latestsub
--FROM vSkillsStudentOnlyAnswer_AvgPivot
--WHERE vSkillsStudentOnlyAnswer_AvgPivot.AcademicYearID = @AcademicYearID

--Now, (for Overall) we need to find the Most Recent Period and the StartingValue and the Latest Value.
SELECT
OGP_StudentID,
	(CASE WHEN Period1 IS NULL THEN
		(CASE WHEN Period2 IS NULL THEN
			(CASE WHEN Period3 IS NULL THEN
				(CASE WHEN Period4 IS NULL THEN
					(CASE WHEN Period5 IS NULL THEN
						(CASE WHEN Period6 IS NULL THEN
							(CASE WHEN Period7 IS NULL THEN
								(CASE WHEN Period8 IS NULL THEN
									(CASE WHEN Period9 IS NULL THEN
										(CASE WHEN Period10 IS NULL THEN
										NULL
										ELSE Period10 END)
									ELSE Period9 END)
								ELSE Period8 END)
							ELSE Period7 END) 
						ELSE Period6 END)
					ELSE Period5 END)
				ELSE Period4 END)
			ELSE Period3 END)
		ELSE Period2 END)
	ELSE Period1 END)
AS StartingPeriodValue,

	(CASE WHEN Period1 IS NULL THEN
		(CASE WHEN  Period2 IS NULL THEN
			(CASE WHEN  Period3 IS NULL THEN
				(CASE WHEN  Period4 IS NULL THEN
					(CASE WHEN  Period5 IS NULL THEN
						(CASE WHEN  Period6 IS NULL THEN
							(CASE WHEN  Period7 IS NULL THEN
								(CASE WHEN  Period8 IS NULL THEN
									(CASE WHEN  Period9 IS NULL THEN
										(CASE WHEN  Period10 IS NULL THEN
										NULL
										ELSE 10 END)
									ELSE 9 END)
								ELSE 8 END)
							ELSE 7 END) 
						ELSE 6 END)
					ELSE 5 END)
				ELSE 4 END)
			ELSE 3 END)
		ELSE 2 END)
	ELSE 1 END)
AS StartingPeriod,

	(CASE WHEN  Period10 IS NULL THEN
		(CASE WHEN  Period9 IS NULL THEN
			(CASE WHEN  Period8 IS NULL THEN
				(CASE WHEN  Period7 IS NULL THEN
					(CASE WHEN  Period6 IS NULL THEN
						(CASE WHEN  Period5 IS NULL THEN
							(CASE WHEN  Period4 IS NULL THEN
								(CASE WHEN  Period3 IS NULL THEN
									(CASE WHEN  Period2 IS NULL THEN
										(CASE WHEN  Period1 IS NULL THEN
										NULL
										ELSE  Period1 END)
									ELSE  Period2 END)
								ELSE  Period3 END)
							ELSE  Period4 END) 
						ELSE  Period5 END)
					ELSE  Period6 END)
				ELSE  Period7 END)
			ELSE  Period8 END)
		ELSE  Period9 END)
	ELSE  Period10 END)
AS MostRecentPeriodValue,

	(CASE WHEN  Period10 IS NULL THEN
		(CASE WHEN  Period9 IS NULL THEN
			(CASE WHEN  Period8 IS NULL THEN
				(CASE WHEN  Period7 IS NULL THEN
					(CASE WHEN  Period6 IS NULL THEN
						(CASE WHEN  Period5 IS NULL THEN
							(CASE WHEN  Period4 IS NULL THEN
								(CASE WHEN  Period3 IS NULL THEN
									(CASE WHEN  Period2 IS NULL THEN
										(CASE WHEN  Period1 IS NULL THEN
										NULL
										ELSE 1 END)
									ELSE 2 END)
								ELSE 3 END)
							ELSE 4 END) 
						ELSE 5 END)
					ELSE 6 END)
				ELSE 7 END)
			ELSE 8 END)
		ELSE 9 END)
	ELSE 6 END)
AS MostRecentPeriod
INTO #TempvSkillsOverallStudentAnswerAvg_Latestsub
FROM vSkillsOverallStudentAnswer_AvgPivot
WHERE vSkillsOverallStudentAnswer_AvgPivot.AcademicYearID = @AcademicYearID


--and (for Student) this does the actual difference between the most recent value and the starting value.
--SELECT 
--	OGP_StudentID,
--	MostRecentPeriod,
--	StartingPeriod,
--	(CASE WHEN StartingPeriodValue IS NULL THEN NULL ELSE (CASE WHEN MostRecentPeriodValue IS NULL THEN NULL ELSE (MostRecentPeriodValue - StartingPeriodValue) END) END) AS Diff_Start_Latest
--INTO #TempvSkillsStudentAvg_Latest
--FROM
--	#TempvSkillsStudentOnlyAnswerAvg_Latestsub

--and (for Overall) this does the actual difference between the most recent value and the starting value.
SELECT 
	OGP_StudentID,
	MostRecentPeriod,
	StartingPeriod,
	(CASE WHEN StartingPeriodValue IS NULL THEN NULL ELSE (CASE WHEN MostRecentPeriodValue IS NULL THEN NULL ELSE (MostRecentPeriodValue - StartingPeriodValue) END) END) AS Diff_Start_Latest
INTO #TempvSkillsOverallAvg_Latest
FROM
	#TempvSkillsOverallStudentAnswerAvg_Latestsub

	
--We want all enrolment details now from VA unioned with EM
SELECT
	OGP_Student.ID AS OGP_StudentID,
	OGP_Student.AcademicYearID, StudentRef, Surname, Forenames, DOB, EthnicityID, Gender, 
	UserDefinedString1, UserDefinedString2, UserDefinedNumber, UserDefinedDate, UserDefinedString3, UserDefinedString4, 
	LLDDandHealthProblemID, FreeSchoolMeal, 
	UserDefinedString5, UserDefinedString6, UserDefinedString7, UserDefinedString8, UserDefinedNumber2,

	 OGP_Student.ID AS OGP_StudentID,
	-- vSkillsStudentOnlyAnswer_AvgPivot.Period1 AS StuPeriod1,  vSkillsStudentOnlyAnswer_AvgPivot.Period2 AS StuPeriod2,  vSkillsStudentOnlyAnswer_AvgPivot.Period3 AS StuPeriod3, vSkillsStudentOnlyAnswer_AvgPivot.Period4 AS StuPeriod4,  vSkillsStudentOnlyAnswer_AvgPivot.Period5 AS StuPeriod5,  
	-- vSkillsStudentOnlyAnswer_AvgPivot.Period6 AS StuPeriod6,  vSkillsStudentOnlyAnswer_AvgPivot.Period7 AS StuPeriod7,  vSkillsStudentOnlyAnswer_AvgPivot.Period8 AS StuPeriod8, vSkillsStudentOnlyAnswer_AvgPivot.Period9 AS StuPeriod9,  vSkillsStudentOnlyAnswer_AvgPivot.Period10 AS StuPeriod10,
	--#TempvSkillsStudentAvg_Latest.Diff_Start_Latest AS StuDifference,
	--#TempvSkillsStudentAvg_Latest.StartingPeriod AS StuStartingPeriod,
	--#TempvSkillsStudentAvg_Latest.MostRecentPeriod AS StuMostRecentPeriod,
	
	 vSkillsOverallStudentAnswer_AvgPivot.Period1 AS OverallPeriod1,  vSkillsOverallStudentAnswer_AvgPivot.Period2 AS OverallPeriod2,  vSkillsOverallStudentAnswer_AvgPivot.Period3 AS OverallPeriod3, vSkillsOverallStudentAnswer_AvgPivot.Period4 AS OverallPeriod4, vSkillsOverallStudentAnswer_AvgPivot.Period5 AS OverallPeriod5,  
	 vSkillsOverallStudentAnswer_AvgPivot.Period6 AS OverallPeriod6,  vSkillsOverallStudentAnswer_AvgPivot.Period7 AS OverallPeriod7,  vSkillsOverallStudentAnswer_AvgPivot.Period8 AS OverallPeriod8, vSkillsOverallStudentAnswer_AvgPivot.Period9 AS OverallPeriod9, vSkillsOverallStudentAnswer_AvgPivot.Period10 AS OverallPeriod10,
	#TempvSkillsOverallAvg_Latest.Diff_Start_Latest AS OverallDifference, 
	#TempvSkillsOverallAvg_Latest.StartingPeriod AS OverallStartingPeriod,
	#TempvSkillsOverallAvg_Latest.MostRecentPeriod AS OverallMostRecentPeriod,

	 vSkillsOverallStudentAnswer_CountPivot.Period1 AS OverallNumResponsePeriod1,  vSkillsOverallStudentAnswer_CountPivot.Period2 AS OverallNumResponsePeriod2,  vSkillsOverallStudentAnswer_CountPivot.Period3 AS OverallNumResponsePeriod3, vSkillsOverallStudentAnswer_CountPivot.Period4 AS OverallNumResponsePeriod4, vSkillsOverallStudentAnswer_CountPivot.Period5 AS OverallNumResponsePeriod5,  
	 vSkillsOverallStudentAnswer_CountPivot.Period6 AS OverallNumResponsePeriod6,  vSkillsOverallStudentAnswer_CountPivot.Period7 AS OverallNumResponsePeriod7,  vSkillsOverallStudentAnswer_CountPivot.Period8 AS OverallNumResponsePeriod8, vSkillsOverallStudentAnswer_CountPivot.Period9 AS OverallNumResponsePeriod9, vSkillsOverallStudentAnswer_CountPivot.Period10 AS OverallNumResponsePeriod10

FROM 
	OGP_Student 
	--LEFT JOIN #TempvSkillsStudentAvg_Latest ON OGP_Student.ID =  #TempvSkillsStudentAvg_Latest.OGP_StudentID
	--LEFT JOIN vSkillsStudentOnlyAnswer_AvgPivot ON OGP_Student.ID = vSkillsStudentOnlyAnswer_AvgPivot.OGP_StudentID
	LEFT JOIN #TempvSkillsOverallAvg_Latest ON OGP_Student.ID =  #TempvSkillsOverallAvg_Latest.OGP_StudentID
	LEFT JOIN vSkillsOverallStudentAnswer_AvgPivot ON OGP_Student.ID = vSkillsOverallStudentAnswer_AvgPivot.OGP_StudentID

	LEFT JOIN vSkillsOverallStudentAnswer_CountPivot ON OGP_Student.ID = vSkillsOverallStudentAnswer_CountPivot.OGP_StudentID

WHERE
	OGP_Student.AcademicYearID = @AcademicYearID
	AND OGP_Student.RecordStatus <> 'Obsolete'




GO



if exists(select name from sys.objects where type = 'P' and name = 'sp_WebPlus_Skills_Reporting_ByCourse_Select')
BEGIN
	DROP PROCEDURE [sp_WebPlus_Skills_Reporting_ByCourse_Select]
END
GO



SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [sp_WebPlus_Skills_Reporting_ByCourse_Select]
  @AcademicYearID   AS VARCHAR(5)
AS
--AMG 28/12/2023
--The DataSource for the main reporting page in OneGradePlus for Skills
--We need to calculate the starting point and the MostRecentPoint
--So if a student starts in period 3, that is their starting point - we can then calculate the difference between the start and end point.
--Shows the Course Answers for the Course and the Overall Student Answers.

----Now, (for Student) we need to find the Most Recent Period and the StartingValue and the Latest Value.
--SELECT
--OGP_StudentID,
--	(CASE WHEN Period1 IS NULL THEN
--		(CASE WHEN Period2 IS NULL THEN
--			(CASE WHEN Period3 IS NULL THEN
--				(CASE WHEN Period4 IS NULL THEN
--					(CASE WHEN Period5 IS NULL THEN
--						(CASE WHEN Period6 IS NULL THEN
--							(CASE WHEN Period7 IS NULL THEN
--								(CASE WHEN Period8 IS NULL THEN
--									(CASE WHEN Period9 IS NULL THEN
--										(CASE WHEN Period10 IS NULL THEN
--										NULL
--										ELSE Period10 END)
--									ELSE Period9 END)
--								ELSE Period8 END)
--							ELSE Period7 END) 
--						ELSE Period6 END)
--					ELSE Period5 END)
--				ELSE Period4 END)
--			ELSE Period3 END)
--		ELSE Period2 END)
--	ELSE Period1 END)

--AS StartingPeriodValue,

--	(CASE WHEN Period1 IS NULL THEN
--		(CASE WHEN  Period2 IS NULL THEN
--			(CASE WHEN  Period3 IS NULL THEN
--				(CASE WHEN  Period4 IS NULL THEN
--					(CASE WHEN  Period5 IS NULL THEN
--						(CASE WHEN  Period6 IS NULL THEN
--							(CASE WHEN  Period7 IS NULL THEN
--								(CASE WHEN  Period8 IS NULL THEN
--									(CASE WHEN  Period9 IS NULL THEN
--										(CASE WHEN  Period10 IS NULL THEN
--										NULL
--										ELSE 10 END)
--									ELSE 9 END)
--								ELSE 8 END)
--							ELSE 7 END) 
--						ELSE 6 END)
--					ELSE 5 END)
--				ELSE 4 END)
--			ELSE 3 END)
--		ELSE 2 END)
--	ELSE 1 END)
--AS StartingPeriod,

--	(CASE WHEN  Period10 IS NULL THEN
--		(CASE WHEN  Period9 IS NULL THEN
--			(CASE WHEN  Period8 IS NULL THEN
--				(CASE WHEN  Period7 IS NULL THEN
--					(CASE WHEN  Period6 IS NULL THEN
--						(CASE WHEN  Period5 IS NULL THEN
--							(CASE WHEN  Period4 IS NULL THEN
--								(CASE WHEN  Period3 IS NULL THEN
--									(CASE WHEN  Period2 IS NULL THEN
--										(CASE WHEN  Period1 IS NULL THEN
--										NULL
--										ELSE  Period1 END)
--									ELSE  Period2 END)
--								ELSE  Period3 END)
--							ELSE  Period4 END) 
--						ELSE  Period5 END)
--					ELSE  Period6 END)
--				ELSE  Period7 END)
--			ELSE  Period8 END)
--		ELSE  Period9 END)
--	ELSE  Period10 END)

--AS MostRecentPeriodValue,

--	(CASE WHEN  Period10 IS NULL THEN
--		(CASE WHEN  Period9 IS NULL THEN
--			(CASE WHEN  Period8 IS NULL THEN
--				(CASE WHEN  Period7 IS NULL THEN
--					(CASE WHEN  Period6 IS NULL THEN
--						(CASE WHEN  Period5 IS NULL THEN
--							(CASE WHEN  Period4 IS NULL THEN
--								(CASE WHEN  Period3 IS NULL THEN
--									(CASE WHEN  Period2 IS NULL THEN
--										(CASE WHEN  Period1 IS NULL THEN
--										NULL
--										ELSE 1 END)
--									ELSE 2 END)
--								ELSE 3 END)
--							ELSE 4 END) 
--						ELSE 5 END)
--					ELSE 6 END)
--				ELSE 7 END)
--			ELSE 8 END)
--		ELSE 9 END)
--	ELSE 6 END)
--AS MostRecentPeriod
--INTO #TempvSkillsStudentOnlyAnswerAvg_Latestsub
--FROM vSkillsStudentOnlyAnswer_AvgPivot
--WHERE AcademicYearID = @AcademicYearID

--Now, (for Course) we need to find the Most Recent Period and the StartingValue and the Latest Value.
SELECT
OGP_StudentID,
CourseID,
CourseCode,
AcademicYearID,

	(CASE WHEN  Period1 IS NULL THEN
		(CASE WHEN  Period2 IS NULL THEN
			(CASE WHEN  Period3 IS NULL THEN
				(CASE WHEN  Period4 IS NULL THEN
					(CASE WHEN  Period5 IS NULL THEN
						(CASE WHEN  Period6 IS NULL THEN
							(CASE WHEN  Period7 IS NULL THEN
								(CASE WHEN  Period8 IS NULL THEN
									(CASE WHEN  Period9 IS NULL THEN
										(CASE WHEN  Period10 IS NULL THEN
										NULL
										ELSE  Period10 END)
									ELSE  Period9 END)
								ELSE  Period8 END)
							ELSE  Period7 END) 
						ELSE  Period6 END)
					ELSE  Period5 END)
				ELSE  Period4 END)
			ELSE  Period3 END)
		ELSE  Period2 END)				
	ELSE  Period1 END)
 AS StartingValue,

	(CASE WHEN  Period1 IS NULL THEN
		(CASE WHEN  Period2 IS NULL THEN
			(CASE WHEN  Period3 IS NULL THEN
				(CASE WHEN  Period4 IS NULL THEN 
					(CASE WHEN  Period5 IS NULL THEN
						(CASE WHEN  Period6 IS NULL THEN
							(CASE WHEN  Period7 IS NULL THEN
								(CASE WHEN  Period8 IS NULL THEN
									(CASE WHEN  Period9 IS NULL THEN
										(CASE WHEN  Period10 IS NULL THEN
										NULL
										ELSE 10 END)
									ELSE 9 END)
								ELSE 8 END)
							ELSE 7 END) 
						ELSE 6 END)
					ELSE 5 END)
				ELSE 4 END)
			ELSE 3 END)
		ELSE 2 END)
	ELSE 1 END)
AS StartingPeriod, 
 
  	(CASE WHEN  Period10 IS NULL THEN
		(CASE WHEN  Period9 IS NULL THEN
			(CASE WHEN  Period8 IS NULL THEN
				(CASE WHEN  Period7 IS NULL THEN
 					(CASE WHEN  Period6 IS NULL THEN
						(CASE WHEN  Period5 IS NULL THEN
							(CASE WHEN  Period4 IS NULL THEN
								(CASE WHEN  Period3 IS NULL THEN
									(CASE WHEN  Period2 IS NULL THEN
										(CASE WHEN  Period1 IS NULL THEN
										NULL
										ELSE  Period1 END)
									ELSE  Period2 END)
								ELSE  Period3 END)
							ELSE  Period4 END) 
						ELSE  Period5 END)
					ELSE  Period6 END)
				ELSE  Period7 END)
			ELSE  Period8 END)
		ELSE  Period9 END)
	ELSE  Period10 END)
AS MostRecentPeriodValue,

	(CASE WHEN  Period10 IS NULL THEN
		(CASE WHEN  Period9 IS NULL THEN
			(CASE WHEN  Period8 IS NULL THEN
				(CASE WHEN  Period7 IS NULL THEN
					(CASE WHEN  Period6 IS NULL THEN
						(CASE WHEN  Period5 IS NULL THEN
							(CASE WHEN  Period4 IS NULL THEN
								(CASE WHEN  Period3 IS NULL THEN
									(CASE WHEN  Period2 IS NULL THEN
										(CASE WHEN  Period1 IS NULL THEN
										NULL
										ELSE 1 END)
									ELSE 2 END)
								ELSE 3 END)
							ELSE 4 END) 
						ELSE 5 END)
					ELSE 6 END)
				ELSE 7 END)
			ELSE 8 END)
		ELSE 9 END)
	ELSE 10 END)
AS MostRecentPeriod
INTO #TempvSkillsCourseStudentOnlyAnswerAvg_Latestsub
FROM vSkillsCourseStudentOnlyAnswer_AvgPivot
WHERE AcademicYearID = @AcademicYearID

--Now, (for Overall) we need to find the Most Recent Period and the StartingValue and the Latest Value.
SELECT
OGP_StudentID,
	(CASE WHEN Period1 IS NULL THEN
		(CASE WHEN Period2 IS NULL THEN
			(CASE WHEN Period3 IS NULL THEN
				(CASE WHEN Period4 IS NULL THEN
					(CASE WHEN Period5 IS NULL THEN
						(CASE WHEN Period6 IS NULL THEN
							(CASE WHEN Period7 IS NULL THEN
								(CASE WHEN Period8 IS NULL THEN
									(CASE WHEN Period9 IS NULL THEN
										(CASE WHEN Period10 IS NULL THEN
										NULL
										ELSE Period10 END)
									ELSE Period9 END)
								ELSE Period8 END)
							ELSE Period7 END) 
						ELSE Period6 END)
					ELSE Period5 END)
				ELSE Period4 END)
			ELSE Period3 END)
		ELSE Period2 END)
	ELSE Period1 END)
AS StartingPeriodValue,

	(CASE WHEN Period1 IS NULL THEN
		(CASE WHEN  Period2 IS NULL THEN
			(CASE WHEN  Period3 IS NULL THEN
				(CASE WHEN  Period4 IS NULL THEN
					(CASE WHEN  Period5 IS NULL THEN
						(CASE WHEN  Period6 IS NULL THEN
							(CASE WHEN  Period7 IS NULL THEN
								(CASE WHEN  Period8 IS NULL THEN
									(CASE WHEN  Period9 IS NULL THEN
										(CASE WHEN  Period10 IS NULL THEN
										NULL
										ELSE 10 END)
									ELSE 9 END)
								ELSE 8 END)
							ELSE 7 END) 
						ELSE 6 END)
					ELSE 5 END)
				ELSE 4 END)
			ELSE 3 END)
		ELSE 2 END)
	ELSE 1 END)
AS StartingPeriod,

	(CASE WHEN  Period10 IS NULL THEN
		(CASE WHEN  Period9 IS NULL THEN
			(CASE WHEN  Period8 IS NULL THEN
				(CASE WHEN  Period7 IS NULL THEN
					(CASE WHEN  Period6 IS NULL THEN
						(CASE WHEN  Period5 IS NULL THEN
							(CASE WHEN  Period4 IS NULL THEN
								(CASE WHEN  Period3 IS NULL THEN
									(CASE WHEN  Period2 IS NULL THEN
										(CASE WHEN  Period1 IS NULL THEN
										NULL
										ELSE  Period1 END)
									ELSE  Period2 END)
								ELSE  Period3 END)
							ELSE  Period4 END) 
						ELSE  Period5 END)
					ELSE  Period6 END)
				ELSE  Period7 END)
			ELSE  Period8 END)
		ELSE  Period9 END)
	ELSE  Period10 END)
AS MostRecentPeriodValue,

	(CASE WHEN  Period10 IS NULL THEN
		(CASE WHEN  Period9 IS NULL THEN
			(CASE WHEN  Period8 IS NULL THEN
				(CASE WHEN  Period7 IS NULL THEN
					(CASE WHEN  Period6 IS NULL THEN
						(CASE WHEN  Period5 IS NULL THEN
							(CASE WHEN  Period4 IS NULL THEN
								(CASE WHEN  Period3 IS NULL THEN
									(CASE WHEN  Period2 IS NULL THEN
										(CASE WHEN  Period1 IS NULL THEN
										NULL
										ELSE 1 END)
									ELSE 2 END)
								ELSE 3 END)
							ELSE 4 END) 
						ELSE 5 END)
					ELSE 6 END)
				ELSE 7 END)
			ELSE 8 END)
		ELSE 9 END)
	ELSE 6 END)
AS MostRecentPeriod
INTO #TempvSkillsOverallStudentAnswerAvg_Latestsub
FROM vSkillsOverallStudentAnswer_AvgPivot
WHERE AcademicYearID = @AcademicYearID


--and (for Student) this does the actual difference between the most recent value and the starting value.
--SELECT 
--	OGP_StudentID,
--	MostRecentPeriod,
--	StartingPeriod,
--	(CASE WHEN StartingPeriodValue IS NULL THEN NULL ELSE (CASE WHEN MostRecentPeriodValue IS NULL THEN NULL ELSE (MostRecentPeriodValue - StartingPeriodValue) END) END) AS Diff_Start_Latest
--INTO #TempvSkillsStudentAvg_Latest
--FROM
--	#TempvSkillsStudentOnlyAnswerAvg_Latestsub

--and (for Course) this does the actual difference between the most recent value and the starting value.
SELECT 
	OGP_StudentID,
	AcademicYearID,
	CourseCode,
	CourseID,
	StartingPeriod,
	MostRecentPeriod,
	(CASE WHEN StartingValue IS NULL THEN NULL ELSE (CASE WHEN MostRecentPeriodValue IS NULL THEN NULL ELSE (MostRecentPeriodValue - StartingValue) END) END) AS Diff_Start_Latest
INTO #TempvSkillsCourseAvg_Latest
FROM
	#TempvSkillsCourseStudentOnlyAnswerAvg_Latestsub

--and (for Overall) this does the actual difference between the most recent value and the starting value.
SELECT 
	OGP_StudentID,
	MostRecentPeriod,
	StartingPeriod,
	(CASE WHEN StartingPeriodValue IS NULL THEN NULL ELSE (CASE WHEN MostRecentPeriodValue IS NULL THEN NULL ELSE (MostRecentPeriodValue - StartingPeriodValue) END) END) AS Diff_Start_Latest
INTO #TempvSkillsOverallAvg_Latest
FROM
	#TempvSkillsOverallStudentAnswerAvg_Latestsub

	
--We want all enrolment details now from VA unioned with EM
SELECT
	EstActVA.AcademicYearID, EstActVA.StudentRef, EstActVA.Forenames, EstActVA.Surname, EstActVA.CourseCode, CourseTitle, 'VA' AS OGP_TypeCode,
	AgeOn31Aug, EstActVA.Gender, Ethnicity, EstActVA.DOB, StartDate, PlannedEndDate, ActualEndDate, NVQLevel, CompletionID, CompletionDescription, EnrolmentGrade
    TeachingGroupCode, TeachingGroupTitle, LearningAimRef, LearningAimTitle, CollegeStructure,
	StudentUserDefinedString1, StudentUserDefinedString2, StudentUserDefinedString3, StudentUserDefinedString4
	StudentUserDefinedString5, StudentUserDefinedString6, StudentUserDefinedString7, StudentUserDefinedString8,
	CourseUserDefinedString1, CourseUserDefinedString2, CourseUserDefinedString3, CourseUserDefinedString4,
	EnrolmentUserDefinedString1, EnrolmentUserDefinedString2, EnrolmentUserDefinedString3, EnrolmentUserDefinedString4,
	EnrolmentUserDefinedString5, EnrolmentUserDefinedString6, EnrolmentUserDefinedString7, EnrolmentUserDefinedString8,
	LLDDandHealthProblemDefinition, EstActVA.FreeSchoolMeal,

	 OGP_Student.ID AS OGP_StudentID,
	-- vSkillsStudentOnlyAnswer_AvgPivot.Period1 AS StuPeriod1,  vSkillsStudentOnlyAnswer_AvgPivot.Period2 AS StuPeriod2,  vSkillsStudentOnlyAnswer_AvgPivot.Period3 AS StuPeriod3, vSkillsStudentOnlyAnswer_AvgPivot.Period4 AS StuPeriod4,  vSkillsStudentOnlyAnswer_AvgPivot.Period5 AS StuPeriod5,  
	-- vSkillsStudentOnlyAnswer_AvgPivot.Period6 AS StuPeriod6,  vSkillsStudentOnlyAnswer_AvgPivot.Period7 AS StuPeriod7,  vSkillsStudentOnlyAnswer_AvgPivot.Period8 AS StuPeriod8, vSkillsStudentOnlyAnswer_AvgPivot.Period9 AS StuPeriod9,  vSkillsStudentOnlyAnswer_AvgPivot.Period10 AS StuPeriod10,
	--#TempvSkillsStudentAvg_Latest.Diff_Start_Latest AS StuDifference,
	--#TempvSkillsStudentAvg_Latest.StartingPeriod AS StuStartingPeriod,
	--#TempvSkillsStudentAvg_Latest.MostRecentPeriod AS StuMostRecentPeriod,
	
	 vSkillsCourseStudentOnlyAnswer_AvgPivot.Period1 AS CoursePeriod1,  vSkillsCourseStudentOnlyAnswer_AvgPivot.Period2 AS CoursePeriod2,  vSkillsCourseStudentOnlyAnswer_AvgPivot.Period3 AS CoursePeriod3, vSkillsCourseStudentOnlyAnswer_AvgPivot.Period4 AS CoursePeriod4,  vSkillsCourseStudentOnlyAnswer_AvgPivot.Period5 AS CoursePeriod5,  
	 vSkillsCourseStudentOnlyAnswer_AvgPivot.Period6 AS CoursePeriod6,  vSkillsCourseStudentOnlyAnswer_AvgPivot.Period7 AS CoursePeriod7,  vSkillsCourseStudentOnlyAnswer_AvgPivot.Period8 AS CoursePeriod8,  vSkillsCourseStudentOnlyAnswer_AvgPivot.Period9 AS CoursePeriod9, vSkillsCourseStudentOnlyAnswer_AvgPivot.Period10 AS CoursePeriod10,
	#TempvSkillsCourseAvg_Latest.Diff_Start_Latest AS CourseDifference, 
	#TempvSkillsCourseAvg_Latest.StartingPeriod AS CourseStartingPeriod,
	#TempvSkillsCourseAvg_Latest.MostRecentPeriod AS CourseMostRecentPeriod,

	 vSkillsCourseStudentOnlyAnswer_CountPivot.Period1 AS CourseNumResponsePeriod1,  vSkillsCourseStudentOnlyAnswer_CountPivot.Period2 AS CourseNumResponsePeriod2,  vSkillsCourseStudentOnlyAnswer_CountPivot.Period3 AS CourseNumResponsePeriod3, vSkillsCourseStudentOnlyAnswer_CountPivot.Period4 AS CourseNumResponsePeriod4,  vSkillsCourseStudentOnlyAnswer_CountPivot.Period5 AS CourseNumResponsePeriod5,  
	 vSkillsCourseStudentOnlyAnswer_CountPivot.Period6 AS CourseNumResponsePeriod6,  vSkillsCourseStudentOnlyAnswer_CountPivot.Period7 AS CourseNumResponsePeriod7,  vSkillsCourseStudentOnlyAnswer_CountPivot.Period8 AS CourseNumResponsePeriod8,  vSkillsCourseStudentOnlyAnswer_CountPivot.Period9 AS CourseNumResponsePeriod9, vSkillsCourseStudentOnlyAnswer_CountPivot.Period10 AS CourseNumResponsePeriod10,

	 vSkillsOverallStudentAnswer_AvgPivot.Period1 AS OverallPeriod1,  vSkillsOverallStudentAnswer_AvgPivot.Period2 AS OverallPeriod2,  vSkillsOverallStudentAnswer_AvgPivot.Period3 AS OverallPeriod3, vSkillsOverallStudentAnswer_AvgPivot.Period4 AS OverallPeriod4, vSkillsOverallStudentAnswer_AvgPivot.Period5 AS OverallPeriod5,  
	 vSkillsOverallStudentAnswer_AvgPivot.Period6 AS OverallPeriod6,  vSkillsOverallStudentAnswer_AvgPivot.Period7 AS OverallPeriod7,  vSkillsOverallStudentAnswer_AvgPivot.Period8 AS OverallPeriod8, vSkillsOverallStudentAnswer_AvgPivot.Period9 AS OverallPeriod9, vSkillsOverallStudentAnswer_AvgPivot.Period10 AS OverallPeriod10,
	#TempvSkillsOverallAvg_Latest.Diff_Start_Latest AS OverallDifference, 
	#TempvSkillsOverallAvg_Latest.StartingPeriod AS OverallStartingPeriod,
	#TempvSkillsOverallAvg_Latest.MostRecentPeriod AS OverallMostRecentPeriod,

	 vSkillsOverallStudentAnswer_CountPivot.Period1 AS OverallNumResponsePeriod1,  vSkillsOverallStudentAnswer_CountPivot.Period2 AS OverallNumResponsePeriod2,  vSkillsOverallStudentAnswer_CountPivot.Period3 AS OverallNumResponsePeriod3, vSkillsOverallStudentAnswer_CountPivot.Period4 AS OverallNumResponsePeriod4, vSkillsOverallStudentAnswer_CountPivot.Period5 AS OverallNumResponsePeriod5,  
	 vSkillsOverallStudentAnswer_CountPivot.Period6 AS OverallNumResponsePeriod6,  vSkillsOverallStudentAnswer_CountPivot.Period7 AS OverallNumResponsePeriod7,  vSkillsOverallStudentAnswer_CountPivot.Period8 AS OverallNumResponsePeriod8, vSkillsOverallStudentAnswer_CountPivot.Period9 AS OverallNumResponsePeriod9, vSkillsOverallStudentAnswer_CountPivot.Period10 AS OverallNumResponsePeriod10


FROM 
	EstActVA
	LEFT JOIN OGP_Student ON EstActVA.StudentRef = OGP_Student.StudentRef AND EstActVA.AcademicYearID = OGP_Student.AcademicYearID
	--LEFT JOIN #TempvSkillsStudentAvg_Latest ON OGP_Student.ID =  #TempvSkillsStudentAvg_Latest.OGP_StudentID
	--LEFT JOIN vSkillsStudentOnlyAnswer_AvgPivot ON OGP_Student.ID = vSkillsStudentOnlyAnswer_AvgPivot.OGP_StudentID
	LEFT JOIN #TempvSkillsCourseAvg_Latest ON OGP_Student.ID =  #TempvSkillsCourseAvg_Latest.OGP_StudentID
		AND EstActVA.CourseCode =  #TempvSkillsCourseAvg_Latest.CourseCode
		AND EstActVA.AcademicYearID =  #TempvSkillsCourseAvg_Latest.AcademicYearID 
	LEFT JOIN vSkillsCourseStudentOnlyAnswer_AvgPivot 
		ON OGP_Student.ID = vSkillsCourseStudentOnlyAnswer_AvgPivot.OGP_StudentID 
		AND EstActVA.CourseCode = vSkillsCourseStudentOnlyAnswer_AvgPivot.CourseCode
		AND EstActVA.AcademicYearID = vSkillsCourseStudentOnlyAnswer_AvgPivot.AcademicYearID
	LEFT JOIN #TempvSkillsOverallAvg_Latest ON OGP_Student.ID =  #TempvSkillsOverallAvg_Latest.OGP_StudentID
	LEFT JOIN vSkillsOverallStudentAnswer_AvgPivot ON OGP_Student.ID = vSkillsOverallStudentAnswer_AvgPivot.OGP_StudentID

	LEFT JOIN vSkillsCourseStudentOnlyAnswer_CountPivot 
		ON OGP_Student.ID = vSkillsCourseStudentOnlyAnswer_CountPivot.OGP_StudentID 
		AND EstActVA.CourseCode = vSkillsCourseStudentOnlyAnswer_CountPivot.CourseCode
		AND EstActVA.AcademicYearID = vSkillsCourseStudentOnlyAnswer_CountPivot.AcademicYearID
	LEFT JOIN vSkillsOverallStudentAnswer_CountPivot ON OGP_Student.ID = vSkillsOverallStudentAnswer_CountPivot.OGP_StudentID

WHERE
	EstActVA.AcademicYearID = @AcademicYearID

UNION 

SELECT
	EngMat_Enrolment_InYear.AcademicYearID, EngMat_Enrolment_InYear.StudentRef, EngMat_Enrolment_InYear.Forenames, EngMat_Enrolment_InYear.Surname, EngMat_Enrolment_InYear.CourseCode, CourseTitle, EngMatType AS OGP_TypeCode,
	AgeOn31Aug, EngMat_Enrolment_InYear.Gender, Ethnicity, EngMat_Enrolment_InYear.DOB, StartDate, PlannedEndDate, ActualEndDate, NotionalNVQLevel, CompletionID, CompletionStatus, EnrolmentGrade
    TeachingGroupCode, TeachingGroupTitle, LearningAimRef, LearningAimRefTitle, CollegeStructure,
	StudentUserDefinedString1, StudentUserDefinedString2, StudentUserDefinedString3, StudentUserDefinedString4
	StudentUserDefinedString5, StudentUserDefinedString6, StudentUserDefinedString7, StudentUserDefinedString8,
	CourseUserDefinedString1, CourseUserDefinedString2, CourseUserDefinedString3, CourseUserDefinedString4,
	EnrolmentUserDefinedString1, EnrolmentUserDefinedString2, EnrolmentUserDefinedString3, EnrolmentUserDefinedString4,
	EnrolmentUserDefinedString5, EnrolmentUserDefinedString6, EnrolmentUserDefinedString7, EnrolmentUserDefinedString8,
	LLDDandHealthProblem, EngMat_Enrolment_InYear.FreeSchoolMeal,

	 OGP_Student.ID AS OGP_StudentID,
	-- vSkillsStudentOnlyAnswer_AvgPivot.Period1 AS StuPeriod1,  vSkillsStudentOnlyAnswer_AvgPivot.Period2 AS StuPeriod2,  vSkillsStudentOnlyAnswer_AvgPivot.Period3 AS StuPeriod3, vSkillsStudentOnlyAnswer_AvgPivot.Period4 AS StuPeriod4,  vSkillsStudentOnlyAnswer_AvgPivot.Period5 AS StuPeriod5,  
	-- vSkillsStudentOnlyAnswer_AvgPivot.Period6 AS StuPeriod6,  vSkillsStudentOnlyAnswer_AvgPivot.Period7 AS StuPeriod7,  vSkillsStudentOnlyAnswer_AvgPivot.Period8 AS StuPeriod8, vSkillsStudentOnlyAnswer_AvgPivot.Period9 AS StuPeriod9,  vSkillsStudentOnlyAnswer_AvgPivot.Period10 AS StuPeriod10,
	--#TempvSkillsStudentAvg_Latest.Diff_Start_Latest AS StuDifference,
	--#TempvSkillsStudentAvg_Latest.StartingPeriod AS StuStartingPeriod,
	--#TempvSkillsStudentAvg_Latest.MostRecentPeriod AS StuMostRecentPeriod,
	
	 vSkillsCourseStudentOnlyAnswer_AvgPivot.Period1 AS CoursePeriod1,  vSkillsCourseStudentOnlyAnswer_AvgPivot.Period2 AS CoursePeriod2,  vSkillsCourseStudentOnlyAnswer_AvgPivot.Period3 AS CoursePeriod3, vSkillsCourseStudentOnlyAnswer_AvgPivot.Period4 AS CoursePeriod4,  vSkillsCourseStudentOnlyAnswer_AvgPivot.Period5 AS CoursePeriod5,  
	 vSkillsCourseStudentOnlyAnswer_AvgPivot.Period6 AS CoursePeriod6,  vSkillsCourseStudentOnlyAnswer_AvgPivot.Period7 AS CoursePeriod7,  vSkillsCourseStudentOnlyAnswer_AvgPivot.Period8 AS CoursePeriod8,  vSkillsCourseStudentOnlyAnswer_AvgPivot.Period9 AS CoursePeriod9, vSkillsCourseStudentOnlyAnswer_AvgPivot.Period10 AS CoursePeriod10,
	#TempvSkillsCourseAvg_Latest.Diff_Start_Latest AS CourseDifference, 
	#TempvSkillsCourseAvg_Latest.StartingPeriod AS CourseStartingPeriod,
	#TempvSkillsCourseAvg_Latest.MostRecentPeriod AS CourseMostRecentPeriod,

	 vSkillsCourseStudentOnlyAnswer_CountPivot.Period1 AS CourseNumResponsePeriod1,  vSkillsCourseStudentOnlyAnswer_CountPivot.Period2 AS CourseNumResponsePeriod2,  vSkillsCourseStudentOnlyAnswer_CountPivot.Period3 AS CourseNumResponsePeriod3, vSkillsCourseStudentOnlyAnswer_CountPivot.Period4 AS CourseNumResponsePeriod4,  vSkillsCourseStudentOnlyAnswer_CountPivot.Period5 AS CourseNumResponsePeriod5,  
	 vSkillsCourseStudentOnlyAnswer_CountPivot.Period6 AS CourseNumResponsePeriod6,  vSkillsCourseStudentOnlyAnswer_CountPivot.Period7 AS CourseNumResponsePeriod7,  vSkillsCourseStudentOnlyAnswer_CountPivot.Period8 AS CourseNumResponsePeriod8,  vSkillsCourseStudentOnlyAnswer_CountPivot.Period9 AS CourseNumResponsePeriod9, vSkillsCourseStudentOnlyAnswer_CountPivot.Period10 AS CourseNumResponsePeriod10,

	 vSkillsOverallStudentAnswer_AvgPivot.Period1 AS OverallPeriod1,  vSkillsOverallStudentAnswer_AvgPivot.Period2 AS OverallPeriod2,  vSkillsOverallStudentAnswer_AvgPivot.Period3 AS OverallPeriod3, vSkillsOverallStudentAnswer_AvgPivot.Period4 AS OverallPeriod4, vSkillsOverallStudentAnswer_AvgPivot.Period5 AS OverallPeriod5,  
	 vSkillsOverallStudentAnswer_AvgPivot.Period6 AS OverallPeriod6,  vSkillsOverallStudentAnswer_AvgPivot.Period7 AS OverallPeriod7,  vSkillsOverallStudentAnswer_AvgPivot.Period8 AS OverallPeriod8, vSkillsOverallStudentAnswer_AvgPivot.Period9 AS OverallPeriod9, vSkillsOverallStudentAnswer_AvgPivot.Period10 AS OverallPeriod10,
	#TempvSkillsOverallAvg_Latest.Diff_Start_Latest AS OverallDifference, 
	#TempvSkillsOverallAvg_Latest.StartingPeriod AS OverallStartingPeriod,
	#TempvSkillsOverallAvg_Latest.MostRecentPeriod AS OverallMostRecentPeriod,

	 vSkillsOverallStudentAnswer_CountPivot.Period1 AS OverallNumResponsePeriod1,  vSkillsOverallStudentAnswer_CountPivot.Period2 AS OverallNumResponsePeriod2,  vSkillsOverallStudentAnswer_CountPivot.Period3 AS OverallNumResponsePeriod3, vSkillsOverallStudentAnswer_CountPivot.Period4 AS OverallNumResponsePeriod4, vSkillsOverallStudentAnswer_CountPivot.Period5 AS OverallNumResponsePeriod5,  
	 vSkillsOverallStudentAnswer_CountPivot.Period6 AS OverallNumResponsePeriod6,  vSkillsOverallStudentAnswer_CountPivot.Period7 AS OverallNumResponsePeriod7,  vSkillsOverallStudentAnswer_CountPivot.Period8 AS OverallNumResponsePeriod8, vSkillsOverallStudentAnswer_CountPivot.Period9 AS OverallNumResponsePeriod9, vSkillsOverallStudentAnswer_CountPivot.Period10 AS OverallNumResponsePeriod10


FROM
	EngMat_Enrolment_InYear
	LEFT JOIN OGP_Student ON EngMat_Enrolment_InYear.StudentRef = OGP_Student.StudentRef AND EngMat_Enrolment_InYear.AcademicYearID = OGP_Student.AcademicYearID
	--LEFT JOIN vSkillsStudentOnlyAnswer_AvgPivot ON OGP_Student.ID =  vSkillsStudentOnlyAnswer_AvgPivot.OGP_StudentID
	--LEFT JOIN #TempvSkillsStudentAvg_Latest ON OGP_Student.ID = #TempvSkillsStudentAvg_Latest.OGP_StudentID
	LEFT JOIN vSkillsCourseStudentOnlyAnswer_AvgPivot ON OGP_Student.ID =  vSkillsCourseStudentOnlyAnswer_AvgPivot.OGP_StudentID
		AND EngMat_Enrolment_InYear.CourseID =  vSkillsCourseStudentOnlyAnswer_AvgPivot.CourseID
	LEFT JOIN #TempvSkillsCourseAvg_Latest ON OGP_Student.ID = #TempvSkillsCourseAvg_Latest.OGP_StudentID 
		AND EngMat_Enrolment_InYear.CourseID = #TempvSkillsCourseAvg_Latest.CourseID
	LEFT JOIN vSkillsOverallStudentAnswer_AvgPivot ON OGP_Student.ID =  vSkillsOverallStudentAnswer_AvgPivot.OGP_StudentID
	LEFT JOIN #TempvSkillsOverallAvg_Latest ON OGP_Student.ID = #TempvSkillsOverallAvg_Latest.OGP_StudentID

	LEFT JOIN vSkillsCourseStudentOnlyAnswer_CountPivot ON OGP_Student.ID =  vSkillsCourseStudentOnlyAnswer_CountPivot.OGP_StudentID
	AND EngMat_Enrolment_InYear.CourseID =  vSkillsCourseStudentOnlyAnswer_CountPivot.CourseID
	LEFT JOIN vSkillsOverallStudentAnswer_CountPivot ON OGP_Student.ID =  vSkillsOverallStudentAnswer_CountPivot.OGP_StudentID

WHERE
	EngMat_Enrolment_InYear.AcademicYearID = @AcademicYearID



GO



